/***********************************************************************************************************************************
 * Name:	CMD_BPC-Out.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Generate BCP.exe format and out commands for all tables in the current database.
 */
SELECT	-- format file command
	cmd= 'BCP "' + DB_NAME() + '.' + SCHEMA_NAME(schema_id) + '.' +  name + '" FORMAT nul -n -k -q -T -x -f "' + SCHEMA_NAME(schema_id) + N'-' + name + '.xml" -S ' + @@SERVERNAME
	FROM sys.tables WHERE type = 'U' AND is_ms_shipped = 0 
UNION ALL
SELECT	-- data file command
	cmd= 'BCP "' + DB_NAME() + '.' + SCHEMA_NAME(schema_id) + '.' +  name + '" OUT "'  + SCHEMA_NAME(schema_id) + N'-' + name + '.dat" -k -q -T -f "' + SCHEMA_NAME(schema_id) + N'-' + name + '.xml" -S ' + @@SERVERNAME
	FROM sys.tables WHERE type = 'U' AND is_ms_shipped = 0
ORDER BY cmd;
GO